CREATE SCHEMA IF NOT EXISTS `mydb`


 GO
 
CREATE  TABLE IF NOT EXISTS `mydb`.`Member` (
  `member_id` INT NOT NULL AUTO_INCREMENT ,
  `first_name` VARCHAR(45) NULL ,
  `last_name` VARCHAR(45) NULL ,
  `email` VARCHAR(45) NULL ,
  `password` VARCHAR(45) NULL ,
  `phoneNum` VARCHAR(45) NULL ,
  `fax` VARCHAR(45) NULL ,
  `address` VARCHAR(200) NULL ,
  `affiliation` VARCHAR(45) NULL ,
  `fb_page` VARCHAR(200) NULL ,
  `linked_page` VARCHAR(200) NULL ,
  `website` VARCHAR(200) NULL ,
  `research_gate_site` VARCHAR(200) NULL ,
  `academic_site` VARCHAR(200) NULL ,
  `mendeley_site` VARCHAR(200) NULL ,
  PRIMARY KEY (`member_id`) )
  
CREATE  TABLE IF NOT EXISTS `mydb`.`E-mail` (
  `member_id` INT NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  
  PRIMARY KEY (`member_id`, `email`) ,

    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`) )
    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`phoneNum` (
  `member_id` INT NOT NULL ,
  `phoneNum` INT NOT NULL ,
  PRIMARY KEY (`member_id`, `phoneNum`) ,
 
    FOREIGN KEY (`member_id`) REFERENCES `mydb`.`Member` (`member_id`) )



CREATE  TABLE IF NOT EXISTS `mydb`.`Privileges` (
  `privileges_id` INT NOT NULL AUTO_INCREMENT ,
  `role` VARCHAR(45) NULL ,
  
  PRIMARY KEY (`privileges_id`) )
  
  
CREATE  TABLE IF NOT EXISTS `mydb`.`Conference` (
  `conference_id` INT NOT NULL AUTO_INCREMENT ,
  `long_name` VARCHAR(45) NULL ,
  `short_name` VARCHAR(45) NULL ,
  `description` VARCHAR(45) NULL ,
  `rev_deadline` VARCHAR(45) NULL ,
  `subm_deadline` VARCHAR(45) NULL ,
  `creator_id` INT NULL ,
  
  PRIMARY KEY (`conference_id`) ,
 
    FOREIGN KEY (`creator_id`) REFERENCES `mydb`.`Member` (`member_id`) )


CREATE  TABLE IF NOT EXISTS `mydb`.`Comment` (
  `comment_id` INT NOT NULL AUTO_INCREMENT ,
  `span_id` INT NULL ,
  `text` VARCHAR(200) NULL ,
  
  PRIMARY KEY (`comment_id`) )
  
  
CREATE  TABLE IF NOT EXISTS `mydb`.`Conversations` (
  `conversation_id` INT NOT NULL AUTO_INCREMENT ,
  `conversation_subject` VARCHAR(45) NULL ,
  
  PRIMARY KEY (`conversation_id`) )
  
  
CREATE  TABLE IF NOT EXISTS `mydb`.`Messages` (
  `message_id` INT NOT NULL AUTO_INCREMENT ,
  `message` LONGTEXT NULL ,
  PRIMARY KEY (`message_id`) )

  
CREATE  TABLE IF NOT EXISTS `mydb`.`Area_Of_Expertise` (
  `area_of_expertise_id` INT NOT NULL AUTO_INCREMENT ,
  `area_of_expertise_text` VARCHAR(45) NULL ,
  PRIMARY KEY (`area_of_expertise_id`) )

  
CREATE  TABLE IF NOT EXISTS `mydb`.`Category` (
  `category_id` INT NOT NULL AUTO_INCREMENT ,
  `category` VARCHAR(45) NULL ,
  PRIMARY KEY (`category_id`) )


CREATE  TABLE IF NOT EXISTS `mydb`.`Session` (
  `session_id` INT NOT NULL ,
  `session_date` DATE NULL ,
  `conference_id` INT NULL ,
  `creator_id` INT NULL ,
  
  PRIMARY KEY (`session_id`) ,
  
    FOREIGN KEY (`conference_id`) REFERENCES `mydb`.`Conference` (`conference_id`) ,

    FOREIGN KEY (`creator_id`) REFERENCES `mydb`.`Member` (`member_id`) )


CREATE  TABLE IF NOT EXISTS `mydb`.`Paper` (
  `paper_id` INT NOT NULL AUTO_INCREMENT ,
  `paper_title` VARCHAR(45) NULL ,
  `category_id` INT NULL ,
  `date_of_submission` DATE NULL ,
  `status` VARCHAR(45) NULL ,
  `author_id` INT NULL ,
  `conference_id` INT NULL ,
  `deleted` TINYINT(1) NULL ,
  `session_id` INT NULL ,
  `acceptance` INT NULL ,
  `final_rating` INT NULL ,

  PRIMARY KEY (`paper_id`) ,

    FOREIGN KEY (`author_id`) REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`category_id`) REFERENCES `mydb`.`Category` (`category_id`),

    FOREIGN KEY (`conference_id`)REFERENCES `mydb`.`Conference` (`conference_id`),

    FOREIGN KEY (`session_id`) REFERENCES `mydb`.`Session` (`session_id`) )


    
CREATE  TABLE IF NOT EXISTS `mydb`.`Version` (
  `version_id` INT NOT NULL AUTO_INCREMENT ,
  `path` LONGTEXT NULL ,
  `paper_id` INT NULL ,
  
  PRIMARY KEY (`version_id`) ,
   
    FOREIGN KEY (`paper_id`) REFERENCES `mydb`.`Paper` (`paper_id`) )
    



CREATE  TABLE IF NOT EXISTS `mydb`.`Limit_Paper` (
  `member_id` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  `category_id` INT NOT NULL ,
  `maximum_papers` INT NULL ,
  `assigned_papers` INT NULL ,
  
  PRIMARY KEY (`member_id`, `conference_id`, `category_id`) ,

    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`conference_id`)REFERENCES `mydb`.`Conference` (`conference_id`),

    FOREIGN KEY (`category_id`) REFERENCES `mydb`.`Category` (`category_id`) )
    

CREATE  TABLE IF NOT EXISTS `mydb`.`Review` (
  `review_id` INT NOT NULL AUTO_INCREMENT ,
  `review_text` VARCHAR(200) NULL ,
  `end_date` DATE NULL ,
  `start_date` DATE NULL ,
  `version_id` INT NULL ,
  `reviewer_id` INT NULL ,
  
  PRIMARY KEY (`review_id`) ,

    FOREIGN KEY (`version_id`) REFERENCES `mydb`.`Version` (`version_id`),

    FOREIGN KEY (`reviewer_id`) REFERENCES `mydb`.`Member` (`member_id`) )



CREATE  TABLE IF NOT EXISTS `mydb`.`Rating_Criteria` (
  `rate_id` INT NOT NULL ,
  `criteria_type` VARCHAR(45) NULL ,
  PRIMARY KEY (`rate_id`) )

    
CREATE  TABLE IF NOT EXISTS `mydb`.`Request_On_Subscribe` (
  `temp_id` INT NOT NULL AUTO_INCREMENT,
  `request_email` VARCHAR(45) NULL ,
  `member_id` INT NULL ,
  `conference_id` INT NULL ,
  PRIMARY KEY (`temp_id`) ,

    FOREIGN KEY (`member_id`) REFERENCES `mydb`.`Member` (`member_id`),
 
    FOREIGN KEY (`conference_id`) REFERENCES `mydb`.`Conference` (`conference_id`) )
    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`Committees` (
  `committee_id` INT NOT NULL AUTO_INCREMENT ,
  `committee_name` VARCHAR(45) NULL ,
  
  PRIMARY KEY (`committee_id`) )


CREATE  TABLE IF NOT EXISTS `mydb`.`A_Rebuttal_Review` (
  `member_id` INT NOT NULL ,
  `review_id` INT NOT NULL ,
  `rebuttal_text` LONGTEXT NULL ,
  
  PRIMARY KEY (`member_id`, `review_id`) ,

    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`review_id`)REFERENCES `mydb`.`Review` (`review_id`) )



CREATE  TABLE IF NOT EXISTS `mydb`.`Conversation_Member` (
  `member_id` INT NOT NULL ,
  `conversation_id` INT NOT NULL ,
  `conversation_last_view` INT NULL ,
  `conversation_deleted` INT NULL ,
  
  PRIMARY KEY (`member_id`, `conversation_id`) ,
 
    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),
    
    FOREIGN KEY (`conversation_id`)REFERENCES `mydb`.`Conversations` (`conversation_id`) )


CREATE  TABLE IF NOT EXISTS `mydb`.`Conversation_Messages` (
  `member_id` INT NOT NULL ,
  `conversation_id` INT NOT NULL ,
  `message_id` INT NOT NULL ,
  `message_date` INT NULL ,
  `message_text` LONGTEXT NULL ,
  
  PRIMARY KEY (`member_id`, `conversation_id`, `message_id`) ,
  
    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`conversation_id`)REFERENCES `mydb`.`Conversations` (`conversation_id`),
 
    FOREIGN KEY (`message_id`)REFERENCES `mydb`.`Messages` (`message_id`) )
    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`Member_Comment_Review` (
  `member_id` INT NOT NULL ,
  `comment_id` INT NOT NULL ,
  `review_id` INT NOT NULL ,
  
  PRIMARY KEY (`member_id`, `comment_id`, `review_id`) ,
 
    FOREIGN KEY (`member_id`) REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`comment_id`) REFERENCES `mydb`.`Comment` (`comment_id`),
 
    FOREIGN KEY (`review_id`) REFERENCES `mydb`.`Review` (`review_id`) )

    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`Member_Message_Member` (
  `member_id1` INT NOT NULL ,
  `member_id2` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  `message` VARCHAR(45) NULL ,
  PRIMARY KEY (`member_id1`, `member_id2`, `conference_id`) ,

    FOREIGN KEY (`member_id1`) REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`member_id2`) REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`conference_id`) REFERENCES `mydb`.`Conference` (`conference_id`) )


    
 
CREATE  TABLE IF NOT EXISTS `mydb`.`Member_Privileges` (
  `member_id` INT NOT NULL ,
  `privileges_id` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  PRIMARY KEY (`member_id`, `privileges_id`, `conference_id`) ,

    FOREIGN KEY (`member_id`) REFERENCES `mydb`.`Member` (`member_id`),
    
    FOREIGN KEY (`privileges_id`) REFERENCES `mydb`.`Privileges` (`privileges_id`),

    FOREIGN KEY (`conference_id`) REFERENCES `mydb`.`Conference` (`conference_id`) )



CREATE  TABLE IF NOT EXISTS `mydb`.`Member_SubscribesTo_Conference` (
  `member_id` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  
  PRIMARY KEY (`member_id`, `conference_id`) ,
  
    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),
   
    FOREIGN KEY (`conference_id`)REFERENCES `mydb`.`Conference` (`conference_id`) )
 
    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`O_OrganizedBy_Conference` (
  `member_id` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  
  PRIMARY KEY (`member_id`, `conference_id`) ,
 
    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),
  
    FOREIGN KEY (`conference_id`)REFERENCES `mydb`.`Conference` (`conference_id`) )


CREATE  TABLE IF NOT EXISTS `mydb`.`O_PostNews_Conference` (
  `member_id` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  `postnews_text` LONGTEXT NULL,
  
  PRIMARY KEY (`member_id`, `conference_id`) ,
 
    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),
 
    FOREIGN KEY (`conference_id`)REFERENCES `mydb`.`Conference` (`conference_id`) )
    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`R_SendFeedback_O` (
  `member_id1` INT NOT NULL ,
  `member_id2` INT NOT NULL ,
  `feedback_text` LONGTEXT NULL ,
  
  PRIMARY KEY (`member_id1`, `member_id2`) ,

    FOREIGN KEY (`member_id1`)REFERENCES `mydb`.`Member` (`member_id`),
 
    FOREIGN KEY (`member_id2`)REFERENCES `mydb`.`Member` (`member_id`) )


CREATE  TABLE IF NOT EXISTS `mydb`.`Request` (
  `member_id1` INT NOT NULL ,
  `member_id2` INT NOT NULL ,
  `request` VARCHAR(45) NULL ,
  `approve` TINYINT(1) NULL ,
  `conference_id` INT NULL ,

  PRIMARY KEY (`member_id1`, `member_id2`) ,

    FOREIGN KEY (`member_id1`)REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`member_id2`)REFERENCES `mydb`.`Member` (`member_id`) )
    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`Assigned_Papers` (
  `member_id` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  `paper_id` INT NOT NULL ,
  PRIMARY KEY (`member_id`, `paper_id`, `conference_id`) ,
 
    FOREIGN KEY (`member_id`) REFERENCES `mydb`.`Member` (`member_id`),
 
    FOREIGN KEY (`conference_id`) REFERENCES `mydb`.`Conference` (`conference_id`),

    FOREIGN KEY (`paper_id`) REFERENCES `mydb`.`Paper` (`paper_id`) )

    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`Member_Expert_Conf` (
  `area_of_expertise_id` INT NOT NULL ,
  `member_id` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  PRIMARY KEY (`area_of_expertise_id`, `member_id`, `conference_id`) ,

    FOREIGN KEY (`conference_id`)REFERENCES `mydb`.`Conference` (`conference_id`),
 
    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`area_of_expertise_id`) REFERENCES `mydb`.`Area_Of_Expertise` (`area_of_expertise_id`) )


CREATE  TABLE IF NOT EXISTS `mydb`.`Conference_Rating_Criteria` (
  `Conference_id` INT NOT NULL ,
  `Rate_id` INT NOT NULL ,
  PRIMARY KEY (`Conference_id`, `Rate_id`) ,
 
    FOREIGN KEY (`conference_id`)REFERENCES `mydb`.`Conference` (`conference_id`),

    FOREIGN KEY (`rate_id`)REFERENCES `mydb`.`Rating_Criteria` (`rate_id`) )
    

CREATE  TABLE IF NOT EXISTS `mydb`.`Version_Comment_Rating_Criteria` (
  `rate_id` INT NOT NULL ,
  `comment_id` INT NOT NULL ,
  `version_id` INT NOT NULL ,
  `rating_value` INT NULL ,
  PRIMARY KEY (`rate_id`, `comment_id`, `version_id`) ,

    FOREIGN KEY (`rate_id`) REFERENCES `mydb`.`Rating_Criteria` (`rate_id`),

    FOREIGN KEY (`comment_id`) REFERENCES `mydb`.`Comment` (`comment_id`),

    FOREIGN KEY (`version_id`) REFERENCES `mydb`.`Version` (`version_id`) )


    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`Member_Conference_Committee` (
  `member_id` INT NOT NULL ,
  `conference_id` INT NOT NULL ,
  `committee_id` INT NOT NULL ,
  
  PRIMARY KEY (`member_id`, `conference_id`, `committee_id`) ,

    FOREIGN KEY (`member_id`)REFERENCES `mydb`.`Member` (`member_id`),

    FOREIGN KEY (`conference_id`)REFERENCES `mydb`.`Conference` (`conference_id`),

    FOREIGN KEY (`committee_id`)REFERENCES `mydb`.`Committees` (`committee_id`) )
    
    
CREATE  TABLE IF NOT EXISTS `mydb`.`Linked_Conferences` (
  `conference_id1` INT NOT NULL ,
  `conference_id2` INT NOT NULL ,
  PRIMARY KEY (`conference_id1`, `conference_id2`) ,

    FOREIGN KEY (`conference_id1`)REFERENCES `mydb`.`Conference` (`conference_id`),
 
    FOREIGN KEY (`conference_id2`)REFERENCES `mydb`.`Conference` (`conference_id`) )

    

